﻿using System;
using System.Collections.Generic;
using System.Data;
using Spring.Data.Common;
using TeamTasker.Contracts;
using TeamTasker.Server.DataAccess.Support;
using Spring.Data;
using TeamTasker.Utils;
using System.Data.SqlTypes;

namespace TeamTasker.Server.DataAccess
{
    public class TaskDAO : BaseDAO<Task>
    {
        public IList<long> GetTaskIDs(long assignedUserID,
                                      DateTime start, DateTime end)
        {
            IDbParameters param = AdoTemplate.CreateDbParameters();
            param.Add("UserID", SqlDbType.BigInt).Value = assignedUserID;
            param.Add("Start", SqlDbType.DateTime).Value = start;
            param.Add("End", SqlDbType.DateTime).Value = end;

            return (IList<long>)AdoTemplate.QueryWithResultSetExtractor(
                CommandType.Text,
                @"select ID from Task where AssignedUserID = @UserID and 
                  and (BaselineStartDate <= @End or ActualStartDate <= @End) 
                  and (BaselineEndDate >= @Start or ActualEndDate >= @Start)",
                new IdListResultSetExtractor(),
                param);
        }

        public override Task Load(long id)
        {
            Task task = LoadForCompare(id);
            if (task != null)
            {
                LoadTaskHistory(task);
                LoadTaskWorkLog(task);
            }
            return task;
        }

        public Task LoadForCompare(long taskID)
        {
            Task task = LoadMainFields(taskID);
            if (task != null)
            {
                task.ID = taskID;
                LoadTaskPredecessors(task);
            }
            return task;
        }

        public override void Save(Task entity)
        {
            if (entity.ID == 0)
            {
                CreateTask(entity);
            }
            else
            {
                UpdateTask(entity);
            }
        }

        public override void Delete(long id)
        {
            throw new NotImplementedException();
        }

        protected Task LoadMainFields(long id)
        {
            IDbParameters param = AdoTemplate.CreateDbParameters();
            param.Add("ID", SqlDbType.BigInt).Value = id;
            return (Task) AdoTemplate.QueryWithResultSetExtractor(
                CommandType.Text,
                @"select 
                    t1.Summary,  
                    t1.Details,  
                    t1.CreationDate,  
                    t1.Estimation,  
                    t1.ParentTaskID,  
                    (select Summary from Task t2 where t2.ID = t1.ParentTaskID),  
                    t1.Priority, 
                    t1.Status,  
                    t1.BaselineStartDate,  
                    t1.BaselineEndDate,  
                    t1.ActualStartDate,  
                    t1.ActualEndDate,  
                    t1.AssignedUserID,  
                    (select FullName from ApplicationUser au where au.ID = t1.AssignedUserID),  
                    t1.AssignmentDate,  
                    t1.UserAllocationPercentage,  
                    t1.IsAssignmentApproved, 
                    t1.AssignmentApprovalDate,  
                    t1.AssignmentApprovedBy,  
                    (select FullName from ApplicationUser au where au.ID = t1.AssignmentApprovedBy) 
                  from Task t1 
                  where ID = @ID",
                new TaskResultSetExtractor(),
                param);
        }

        protected void LoadTaskPredecessors(Task task)
        {
            IDbParameters param = AdoTemplate.CreateDbParameters();
            param.Add("ID", SqlDbType.BigInt).Value = task.ID;
            AdoTemplate.QueryWithRowCallback(
                CommandType.Text,
                @"select t.ID, t.Summary 
                  from Task t inner join Task_Predecessor tp on t.ID = tp.PredecessorID 
                  where tp.TaskID = @ID",
                new IdNameRowCallback(task.Predecessors),
                param);
        }

        protected void LoadTaskHistory(Task task)
        {
            IDbParameters param = AdoTemplate.CreateDbParameters();
            param.Add("TaskID", SqlDbType.BigInt).Value = task.ID;
            AdoTemplate.QueryWithRowCallback(
                CommandType.Text,
                @"select 
                    th.ID, th.UserID, au.FullName, th.Date, 
                    th.FieldName, th.OldValue, th.NewValue 
                  from TaskHistory th, ApplicationUser au 
                  where th.UserID = au.ID and th.TaskID = @TaskID",
                new TaskHistoryRowCallback(task),
                param);
        }

        protected void LoadTaskWorkLog(Task task)
        {
            IDbParameters param = AdoTemplate.CreateDbParameters();
            param.Add("TaskID", SqlDbType.BigInt).Value = task.ID;
            AdoTemplate.QueryWithRowCallback(
                CommandType.Text,
                @"select 
                    tw.ID, tw.Date, tw.HoursWorked, tw.Comment, tw.UserID, au.FullName 
                  from TaskWorkLog tw, ApplicationUser au 
                  where tw.UserID = au.ID and tw.TaskID = @TaskID",
                new TaskWorkLogRowCallback(task),
                param);
        }

        protected void CreateTask(Task task)
        {
            Validate.True(task.ID == 0, "Zero id is expected");
            CreateMainFields(task);
            SaveTaskPredecessors(task);
            SaveTaskWorkLog(task);
        }

        protected void UpdateTask(Task task)
        {
            Validate.True(task.ID > 0, "Non-zero id is expected");
            UpdateMainFields(task);
            SaveTaskPredecessors(task);
            SaveTaskHistory(task);
            SaveTaskWorkLog(task);
        }

        protected void CreateMainFields(Task task)
        {
            IDbParameters param = AdoTemplate.CreateDbParameters();
            param.Add("ID", SqlDbType.BigInt).Value = task.ID = SeqGen.GetNextID();
            SetMainParams(param, task);

            AdoTemplate.ExecuteNonQuery(CommandType.Text,
                @"insert into Task(
                    ID, Summary, Details, CreationDate, Estimation, Priority, Status, ParentTaskID, 
                    BaselineStartDate, BaselineEndDate, ActualStartDate, ActualEndDate, 
                    AssignedUserID, AssignmentDate, UserAllocationPercentage, 
                    IsAssignmentApproved, AssignmentApprovalDate, AssignmentApprovedBy) 
                  values(
                    @ID, @Summary, @Details, @CreationDate, @Estimation, @Priority, @Status, @ParentTaskID, 
                    @BaselineStartDate, @BaselineEndDate, @ActualStartDate, @ActualEndDate, 
                    @AssignedUserID, @AssignmentDate, @UserAllocationPercentage, 
                    @IsAssignmentApproved, @AssignmentApprovalDate, @AssignmentApprovedBy 
                  )",
                param);
        }

        protected void UpdateMainFields(Task task)
        {
            IDbParameters param = AdoTemplate.CreateDbParameters();
            param.Add("ID", SqlDbType.BigInt).Value = task.ID;
            SetMainParams(param, task);

            AdoTemplate.ExecuteNonQuery(CommandType.Text,
                @"update Task set 
                    Summary = @Summary, Details = @Details, 
                    CreationDate = @CreationDate, 
                    Estimation = @Estimation, 
                    Priority = @Priority, 
                    Status = @Status, 
                    ParentTaskID = @ParentTaskID, 
                    BaselineStartDate = @BaselineStartDate, 
                    BaselineEndDate = @BaselineEndDate, 
                    ActualStartDate = @ActualStartDate, 
                    ActualEndDate = @ActualEndDate, 
                    AssignedUserID = @AssignedUserID, 
                    AssignmentDate = @AssignmentDate, 
                    UserAllocationPercentage = @UserAllocationPercentage, 
                    IsAssignmentApproved = @IsAssignmentApproved, 
                    AssignmentApprovalDate = @AssignmentApprovalDate, 
                    AssignmentApprovedBy = @AssignmentApprovedBy
                  where ID = @ID",
                param);
        }

        protected void SetMainParams(IDbParameters param, Task task)
        {
            param.Add("Summary", SqlDbType.NVarChar).Value = task.Summary;
            param.Add("Details", SqlDbType.NVarChar).Value = task.Details;
            param.Add("CreationDate", SqlDbType.DateTime).Value = task.CreationDate;
            param.Add("Estimation", SqlDbType.Float).Value = task.Estimation;
            param.Add("Priority", SqlDbType.Int).Value = (int) task.Priority;
            param.Add("Status", SqlDbType.Int).Value = (int) task.Status;
            param.Add("ParentTaskID", SqlDbType.BigInt).Value = task.ParentTaskID;
            DaoUtils.SetDateTime(param, "BaselineStartDate", task.BaselineStartDate);
            DaoUtils.SetDateTime(param, "BaselineEndDate", task.BaselineEndDate);
            DaoUtils.SetDateTime(param, "ActualStartDate", task.ActualStartDate);
            DaoUtils.SetDateTime(param, "ActualEndDate", task.ActualEndDate);
            param.Add("AssignedUserID", SqlDbType.BigInt).Value = task.AssignedUserID;
            DaoUtils.SetDateTime(param, "AssignmentDate", task.AssignmentDate);
            param.Add("UserAllocationPercentage", SqlDbType.Int).Value = task.UserAllocationPercentage;
            param.Add("IsAssignmentApproved", SqlDbType.Bit).Value = task.IsAssignmentApproved;
            DaoUtils.SetDateTime(param, "AssignmentApprovalDate", task.AssignmentApprovalDate);
            param.Add("AssignmentApprovedBy", SqlDbType.BigInt).Value = task.AssignmentApprovedBy;
        }

        protected void SaveTaskPredecessors(Task task)
        {
            DeletePredecessors(task.ID);
            
            IDbParameters param = AdoTemplate.CreateDbParameters();
            param.Add("TaskID", SqlDbType.BigInt).Value = task.ID;
            param.Add("PredecessorID", SqlDbType.BigInt);
            foreach (long predecessorID in task.Predecessors.Keys)
            {
                param.SetValue("PredecessorID", predecessorID);
                AdoTemplate.ExecuteNonQuery(CommandType.Text,
                    @"insert into Task_Predecessor(TaskID, PredecessorID) 
                      values(@TaskID, @PredecessorID)",
                    param);
            }
        }

        protected void SaveTaskHistory(Task task)
        {
            foreach (TaskHistoryEntry the in task.History)
            {
                if (the.ID == 0)
                {
                    SaveTaskHistoryEntry(task.ID, the);
                }
            }
        }

        protected void SaveTaskHistoryEntry(long taskID, TaskHistoryEntry entry)
        {
            IDbParameters param = AdoTemplate.CreateDbParameters();
            param.Add("ID", SqlDbType.BigInt).Value = entry.ID = SeqGen.GetNextID();
            param.Add("TaskID", SqlDbType.BigInt).Value = entry.TaskID = taskID;
            param.Add("UserID", SqlDbType.BigInt).Value = entry.UserID;
            param.Add("Date", SqlDbType.DateTime).Value = entry.Date;
            param.Add("FieldName", SqlDbType.NVarChar).Value = entry.FieldName;
            DaoUtils.SetText(param, "OldValue", entry.OldValue);
            DaoUtils.SetText(param, "NewValue", entry.NewValue);

            AdoTemplate.ExecuteNonQuery(CommandType.Text,
                @"insert into TaskHistory(
                    ID, TaskID, UserID, Date, FieldName, OldValue, NewValue)
                  values(
                    @ID, @TaskID, @UserID, @Date, @FieldName, @OldValue, @NewValue)",
                param);
        }

        protected void SaveTaskWorkLog(Task task)
        {
            foreach (TaskWorkLogEntry entry in task.WorkLog)
            {
                if (entry.ID == 0)
                {
                    SaveWorkLogEntry(task.ID, entry);
                }
            }
        }

        protected void SaveWorkLogEntry(long taskID, TaskWorkLogEntry entry)
        {
            IDbParameters param = AdoTemplate.CreateDbParameters();
            param.Add("ID", SqlDbType.BigInt).Value = entry.ID = SeqGen.GetNextID();
            param.Add("TaskID", SqlDbType.BigInt).Value = entry.TaskID = taskID;
            param.Add("UserID", SqlDbType.BigInt).Value = entry.UserID;
            param.Add("Comment", SqlDbType.Text).Value = entry.Comment;
            param.Add("HoursWorked", SqlDbType.Float).Value = entry.HoursWorked;
            param.Add("Date", SqlDbType.DateTime).Value = entry.Date;

            AdoTemplate.ExecuteNonQuery(CommandType.Text,
                @"insert into TaskWorkLog(
                    ID, TaskID, UserID, Comment, HoursWorked, Date)
                  values(
                    @ID, @TaskID, @UserID, @Comment, @HoursWorked, @Date)",
                param);
        }

        protected void DeletePredecessors(long taskID)
        {
            IDbParameters param = AdoTemplate.CreateDbParameters();
            param.Add("TaskID", SqlDbType.BigInt).Value = taskID;
            AdoTemplate.ExecuteNonQuery(CommandType.Text,
                "delete from Task_Predecessor where TaskID = @TaskID",
                param);
        }
    }

    class TaskResultSetExtractor : IResultSetExtractor
    {
        public object ExtractData(IDataReader reader)
        {
            Task task = null;
            if (reader.Read())
            {
                task = new Task();
                task.Summary = reader.GetString(0);
                task.Details = reader.GetString(1);
                task.CreationDate = reader.GetDateTime(2); // mustn't be null
                task.Estimation = reader.GetDouble(3);
                task.ParentTaskID = reader.GetInt64(4);
                task.ParentTaskName = reader.GetString(5);
                task.Priority = (TaskPriority) reader.GetInt32(6);
                task.Status = (TaskStatus) reader.GetInt32(7);
                task.BaselineStartDate = DaoUtils.GetDateTime(reader, 8);
                task.BaselineEndDate = DaoUtils.GetDateTime(reader, 9);
                task.ActualStartDate = DaoUtils.GetDateTime(reader, 10);
                task.ActualEndDate = DaoUtils.GetDateTime(reader, 11);
                task.AssignedUserID = reader.GetInt64(12);
                task.AssignedUserName = reader.GetString(13);
                task.AssignmentDate = DaoUtils.GetDateTime(reader, 14);
                task.UserAllocationPercentage = reader.GetInt32(15);
                task.IsAssignmentApproved = reader.GetBoolean(16);
                task.AssignmentApprovalDate = DaoUtils.GetDateTime(reader, 17);
                task.AssignmentApprovedBy = reader.GetInt64(18);
                task.AssignmentApprovedByName = reader.GetString(19);
            }
            return task;
        }
    }

    class TaskHistoryRowCallback : IRowCallback 
    {
        private Task dst;

        public TaskHistoryRowCallback(Task dst)
        {
            Validate.NotNull(dst, "Data storage reference is illegal: null");
            this.dst = dst;
        }

        public void ProcessRow(IDataReader reader)
        {
            TaskHistoryEntry the = new TaskHistoryEntry();
            
            the.ID = reader.GetInt64(0);
            the.TaskID = dst.ID;
            the.UserID = reader.GetInt64(1);
            the.UserName = reader.GetString(2);
            the.Date = reader.IsDBNull(3) ? DateTime.MaxValue : reader.GetDateTime(3);
            the.FieldName = reader.GetString(4);
            the.OldValue = reader.GetString(5);
            the.NewValue = reader.GetString(6);

            dst.History.Add(the);
        }    
    }

    class TaskWorkLogRowCallback : IRowCallback
    {
        private Task dst;

        public TaskWorkLogRowCallback(Task dst)
        {
            Validate.NotNull(dst, "Data storage reference is illegal: null");
            this.dst = dst;
        }

        public void ProcessRow(IDataReader reader)
        {
            TaskWorkLogEntry twl = new TaskWorkLogEntry();

            twl.ID = reader.GetInt64(0);
            twl.TaskID = dst.ID;
            twl.Date = reader.IsDBNull(1) ? DateTime.MaxValue : reader.GetDateTime(1);
            twl.HoursWorked = reader.GetDouble(2);
            twl.Comment = reader.GetString(3);
            twl.UserID = reader.GetInt64(4);
            twl.UserName = reader.GetString(5);

            dst.WorkLog.Add(twl);
        }  
    }

}
